package com.san.common.utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
	private XSSFWorkbook wb = null;

	private XSSFSheet sheet = null;

	/**
	 * @param wb
	 * @param sheet
	 */
	public ExcelUtil(XSSFWorkbook wb, XSSFSheet sheet) {
		this.wb = wb;
		this.sheet = sheet;
	}

	/**
	 * 合并单元格后给合并后的单元格加边框
	 * 
	 * @param region
	 * @param cs
	 */
	public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) {

		int toprowNum = region.getFirstRow();
		for (int i = toprowNum; i <= region.getLastRow(); i++) {
			XSSFRow row = sheet.getRow(i);
			for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
				XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,
												// (short) j);
				cell.setCellStyle(cs);
			}
		}
	}

//	/**
//	 * 设置表头的单元格样式
//	 * 
//	 * @return
//	 */
//	public XSSFCellStyle getHeadStyle() {
//		// 创建单元格样式
//		XSSFCellStyle cellStyle = wb.createCellStyle();
//		// 设置单元格的背景颜色为淡蓝色
//		cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
//		cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
//		// 设置单元格居中对齐
//		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//		// 设置单元格垂直居中对齐
//		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
//		// 创建单元格内容显示不下时自动换行
//		cellStyle.setWrapText(true);
//		// 设置单元格字体样式
//		XSSFFont font = wb.createFont();
//		// 设置字体加粗
//		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
//		font.setFontName("宋体");
//		font.setFontHeight((short) 200);
//		cellStyle.setFont(font);
//		// 设置单元格边框为细线条
//		cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
//		cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
//		cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
//		cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
//		return cellStyle;
//	}
//
//	/**
//	 * 设置表体的单元格样式
//	 * 
//	 * @return
//	 */
//	public XSSFCellStyle getBodyStyle() {
//		// 创建单元格样式
//		XSSFCellStyle cellStyle = wb.createCellStyle();
//		// 设置单元格居中对齐
//		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//		// 设置单元格垂直居中对齐
//		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
//		// 创建单元格内容显示不下时自动换行
//		cellStyle.setWrapText(true);
//		// 设置单元格字体样式
//		XSSFFont font = wb.createFont();
//		// 设置字体加粗
//		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
//		font.setFontName("宋体");
//		font.setFontHeight((short) 200);
//		cellStyle.setFont(font);
//		// 设置单元格边框为细线条
//		cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
//		cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
//		cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
//		cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
//		return cellStyle;
//	}
	
	/**
	 * 读取Excel值
	 * @param input
	 * @param fileType
	 * @param loadTitle
	 * @return
	 * @throws IOException
	 */
	public static List<List<String>> read(InputStream input,String fileType,boolean loadTitle) throws IOException{
		List<List<String>> result = new ArrayList<List<String>>();
		Workbook workbook = null;
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		
		if (StringUtils.isEmpty(fileType)) {
			return result;
		}else if ("xls".equals(fileType)) {
			workbook = new HSSFWorkbook(input);
		} else if ("xlsx".equals(fileType)) {
			workbook = new XSSFWorkbook(input);
		} else {
			return result;
		}
		
		int start = 0;
		if (!loadTitle) {
			start = 1;
		}
		
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			sheet = workbook.getSheetAt(numSheet);
			if (sheet == null) {
				continue;
			}
			row = sheet.getRow(0);
			int maxCellNum = row.getLastCellNum();//总列数已第一行为基准
			for (int rowNum = start; rowNum <= sheet.getLastRowNum(); rowNum++) {
				row = sheet.getRow(rowNum);
				List<String> rowList = new ArrayList<String>();
				for (int i = 0; i < maxCellNum; i++) {
					cell = row.getCell(i);
					if (cell == null) {
						rowList.add("");
					} else {
						rowList.add(getValue(cell));
					}
				}
				result.add(rowList);
			}
		}
		workbook.close();
		return result;
	}

	// 导入excel
	public static List<List<String>> readXlsx(String path) throws IOException {
		InputStream input = new FileInputStream(path);
		return readXlsx(input);
	}

	public static List<List<String>> readXls(String path) throws IOException {
		InputStream input = new FileInputStream(path);
		return readXls(input);
	}

	/*
	 * public static List<List<String>> readXlsx(InputStream input) throws
	 * IOException { List<List<String>> result = new ArrayList<List<String>>();
	 * XSSFWorkbook workbook = new XSSFWorkbook(input); for (XSSFSheet xssfSheet :
	 * workbook) { if (xssfSheet == null) { continue; } for (int rowNum = 1; rowNum
	 * <= xssfSheet.getLastRowNum(); rowNum++) { XSSFRow row =
	 * xssfSheet.getRow(rowNum); int minCellNum = row.getFirstCellNum(); int
	 * maxCellNum = row.getLastCellNum(); List<String> rowList = new
	 * ArrayList<String>(); for (int i = minCellNum; i < maxCellNum; i++) { XSSFCell
	 * cell = row.getCell(i); if (cell == null) { continue; }
	 * rowList.add(cell.toString()); } result.add(rowList); } } return result; }
	 */
	public static List<List<String>> readXlsx(InputStream input) throws IOException {
		List<List<String>> result = new ArrayList<List<String>>();
		XSSFWorkbook workbook = new XSSFWorkbook(input);
		for (Sheet xssfSheet : workbook) {
			if (xssfSheet == null) {
				continue;
			}
			for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow row = (XSSFRow) xssfSheet.getRow(rowNum);
				int minCellNum = row.getFirstCellNum();
				int maxCellNum = row.getLastCellNum();
				
				List<String> rowList = new ArrayList<String>();
				for (int i = minCellNum; i < maxCellNum; i++) {
					XSSFCell cell = row.getCell(i);
					if (cell == null) {
						continue;
					}
					rowList.add(getValue(cell));
				}
				result.add(rowList);
			}
		}
		workbook.close();
		return result;
	}

	public static List<List<String>> readXls(InputStream input) throws IOException {
		List<List<String>> result = new ArrayList<List<String>>();
		HSSFWorkbook workbook = new HSSFWorkbook(input);
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet sheet = workbook.getSheetAt(numSheet);
			if (sheet == null) {
				continue;
			}
			for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
				HSSFRow row = sheet.getRow(rowNum);
				int minCellNum = row.getFirstCellNum();
				int maxCellNum = row.getLastCellNum();
				List<String> rowList = new ArrayList<String>();
				for (int i = minCellNum; i < maxCellNum; i++) {
					HSSFCell cell = row.getCell(i);
					if (cell == null) {
						continue;
					}
					rowList.add(getValue(cell));
				}
				result.add(rowList);
			}
		}
		workbook.close();
		return result;
	}

	/**
	 * 获取一个单元格的值
	 * 
	 * @param cell
	 * @return
	 */
	private static String getValue(Cell cell) {
		if (cell == null) {
			return "";
		}

		String strCell;
		switch (cell.getCellTypeEnum()) {
		case STRING:
			strCell = cell.getStringCellValue();
			break;
		case NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				strCell = sdf.format(cell.getDateCellValue());
			} else {
				strCell = String.valueOf(cell.getNumericCellValue());
			}
			break;
		case BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (StringUtils.isEmpty(strCell)) {
			return "";
		}

		return strCell;
	}
	
	/**
	 * 设置一个单元格的值
	 * @param cell
	 * @param o
	 */
	public static void setValue(Cell cell, Object o) {
        if (o instanceof Date) {
            // 处理时间类型格式化
            SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            cell.setCellValue(sf.format(o));
        } else if (o instanceof Calendar) {
            cell.setCellValue((Calendar) o);
        } else {
            cell.setCellValue(String.valueOf(o));
        }
        
    }
	
	/**
	 * 设置单元格宽度
	 * @param sheet
	 * @param cell
	 */
	public static void setWidth(Sheet sheet,Cell cell) {
		int valWidth = getValue(cell).length() * 768;//256 * 3
		sheet.setColumnWidth(cell.getColumnIndex(), valWidth);
	}

}
